<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/7/30/030
 * Time: 14:22
 */

namespace tool;


use think\Exception;

/**
 * 导出工具类
 * Class ExcelUtil
 * @package tools
 */
class ExcelUtil
{
    private $title = array();//标题数据
    private $hasTitle = false;//是否存在标题
    private $titleLine = 0;//标题所占行数
    private $hasIndex = true;//第一列是否为序号列 与$dataHead 互斥
    private $head = array();//表头数据
    private $hasHead = false;//是否存在表头
    private $headLine = 0;//表头所占行数
    private $data = array();//表体数据
    private $dataKV = array();//列对应关系
    private $isDataKV = false;//是否使用对应关系
    private $hasData = false;//是否存在表体
    private $dataHead = array();//数据部分的左边表头
    private $isDataHead = false;//是否使用数据部分的左边表头
    private $dataHeadRow = 0;//数据部分表头所占列数
    private $dataTotal = array();//统计行数据对象
    private $hasTotal = false;//是否存在统计行
    private $totalIsFoot = false;//统计行存在顶部还是底部
    private $objPHPExcel;//PHPExcel对象
    private $fileName = "导出excel";//导出文件的文件名
    private $xlsName = "导出excel";//导出文件的header显示的标题
    private $excelType = "Excel2007";//导出Excel类型 Excel5 为xls Excel2007 为 xlsx  Excel5
    private $lineCount = 0;//行计数
    private $rowCount = 0;//列计数

    public function __construct()
    {
        //        include_once EXTEND_PATH . 'PHPExcel/PHPExcel/PHPExcel.php';
        include_once '../vendor/PHPExcel/PHPExcel.php';
        $objPHPExcel = new \PHPExcel();
        $this->objPHPExcel = $objPHPExcel;
    }

    private function setTableHead(array $head, $isTableHead = true, $line = 0)
    {
        if ($isTableHead) {
            $this->head = $head;
        }
        if (empty($head)) {
            throw new Exception("第一个参数不能为空数组");
        }
        $objPHPExcel = $this->objPHPExcel;
        $num = 1;//列计数
        if (empty($line)) {
            $line = (!empty($this->titleLine) ? ($this->titleLine + $this->headLine) : $line);//一般表头前面会有一行整体的标题，需要自己写
            if ($isTableHead) {
                $line = $line + 1;
            }
        }
        for ($a = 0; $a < count($head); $a++) {
            $da = $head[$a];
            if (is_array($da[0])) {
                $value = $da[0][0];
                $cell = $da[0][1] - 1;
                $row = $da[0][2] - 1;
                $cells = $this->getExcelIndex($num) . $line . ':' . $this->getExcelIndex($num + $row) . ($line + $cell);//合并列
                $objPHPExcel->getActiveSheet()->mergeCells($cells);
                $objPHPExcel->getActiveSheet()->setCellValue($this->getExcelIndex($num) . $line, $value);
                for ($b = 1; $b < count($da); $b++) {//自己本身不参与下一次显示 下标从1开始
                    $line1 = $line + 1;/*行数加一*/
                    $db = $da[$b];
                    if (is_array($db[0])) {
                        $value = $db[0][0];
                        $cell = $db[0][1] - 1;
                        $row = $db[0][2] - 1;
                        $cells = $this->getExcelIndex($num) . $line1 . ':' . $this->getExcelIndex($num + $row) . ($line1 + $cell);//合并列
                        $objPHPExcel->getActiveSheet()->mergeCells($cells);
                        $objPHPExcel->getActiveSheet()->setCellValue($this->getExcelIndex($num) . $line1, $value);
                        for ($c = 1; $c < count($db); $c++) {
                            $line2 = $line1 + 1;/*行数加一*/
                            $dc = $db[$c];
                            if (is_array($dc[0])) {
                                $value = $dc[0][0];
                                $cell = $dc[0][1] - 1;
                                $row = $dc[0][2] - 1;
                                $cells = $this->getExcelIndex($num) . $line2 . ':' . $this->getExcelIndex($num + $row) . ($line2 + $cell);//合并列
                                $objPHPExcel->getActiveSheet()->mergeCells($cells);
                                $objPHPExcel->getActiveSheet()->setCellValue($this->getExcelIndex($num) . $line2, $value);
                                for ($d = 1; $d < count($dc); $d++) {
                                    $line3 = $line2 + 1;/*行数加一*/
                                    $dd = $dc[$d];
                                    if (is_array($dd[0])) {
                                        $value = $dd[0][0];
                                        $cell = $dd[0][1] - 1;
                                        $row = $dd[0][2] - 1;
                                        $cells = $this->getExcelIndex($num) . $line3 . ':' . $this->getExcelIndex($num + $row) . ($line3 + $cell);//合并列
                                        $objPHPExcel->getActiveSheet()->mergeCells($cells);
                                        $objPHPExcel->getActiveSheet()->setCellValue($this->getExcelIndex($num) . $line3, $value);
                                        for ($e = 1; $e < count($dd); $e++) {
                                            $line4 = $line3 + 1;/*行数加一*/
                                            $de = $dd[$e];
                                            if (is_array($de[0])) {
                                                $value = $de[0][0];
                                                $cell = $de[0][1] - 1;
                                                $row = $de[0][2] - 1;
                                                $cells = $this->getExcelIndex($num) . $line3 . ':' . $this->getExcelIndex($num + $row) . ($line3 + $cell);//合并列
                                                $objPHPExcel->getActiveSheet()->mergeCells($cells);
                                                $objPHPExcel->getActiveSheet()->setCellValue($this->getExcelIndex($num) . $line3, $value);
                                            } else {
                                                $value = $de[0];
                                                $cell = $de[1] - 1;
                                                $row = $de[2] - 1;
                                                $cells = $this->getExcelIndex($num) . $line4 . ':' . $this->getExcelIndex($num + $row) . ($line4 + $cell);//合并列
                                                $objPHPExcel->getActiveSheet()->mergeCells($cells);
                                                $objPHPExcel->getActiveSheet()->setCellValue($this->getExcelIndex($num) . $line4, $value);
                                                $num += ($row + 1);
                                            }
                                        }
                                    } else {
                                        $value = $dd[0];
                                        $cell = $dd[1] - 1;
                                        $row = $dd[2] - 1;
                                        $cells = $this->getExcelIndex($num) . $line3 . ':' . $this->getExcelIndex($num + $row) . ($line3 + $cell);//合并列
                                        $objPHPExcel->getActiveSheet()->mergeCells($cells);
                                        $objPHPExcel->getActiveSheet()->setCellValue($this->getExcelIndex($num) . $line3, $value);
                                        $num += ($row + 1);
                                    }
                                }
                            } else {
                                $value = $dc[0];
                                $cell = $dc[1] - 1;
                                $row = $dc[2] - 1;
                                $cells = $this->getExcelIndex($num) . $line2 . ':' . $this->getExcelIndex($num + $row) . ($line2 + $cell);//合并列
                                $objPHPExcel->getActiveSheet()->mergeCells($cells);
                                $objPHPExcel->getActiveSheet()->setCellValue($this->getExcelIndex($num) . $line2, $value);
                                $num += ($row + 1);
                            }
                        }
                    } else {
                        //兼容性处理
                        $value = $db;
                        $cell = 0;
                        $row = 0;
                        if (is_array($db)) {
                            $value = $db[0];
                            $cell = $db[1] - 1;
                            $row = $db[2] - 1;
                        }
                        $cells = $this->getExcelIndex($num) . $line1 . ':' . $this->getExcelIndex($num + $row) . ($line1 + $cell);//合并列
                        $objPHPExcel->getActiveSheet()->mergeCells($cells);
                        $objPHPExcel->getActiveSheet()->setCellValue($this->getExcelIndex($num) . $line1, $value);
                        $num += ($row + 1);
                    }
                }
            } else {
                //兼容性处理
                $value = $da;
                $cell = 0;
                $row = 0;
                if (is_array($da)) {
                    $value = $da[0];
                    $cell = $da[1] - 1;
                    if (count($da) == 2) {
                        $row = (empty($data) ? 0 : count($data));
                    } else {
                        $row = $da[2] - 1;
                    }
                }
                $cells = $this->getExcelIndex($num) . $line . ':' . $this->getExcelIndex($num + $row) . ($line + $cell);//合并列
                $objPHPExcel->getActiveSheet()->mergeCells($cells);
                $objPHPExcel->getActiveSheet()->setCellValue($this->getExcelIndex($num) . $line, $value);
                $num += ($row + 1);
            }
        }
        if ($isTableHead) {
            $this->headLine = (empty($line4) ? (empty($line3) ? (empty($line2) ? (empty($line1) ? $line : $line1) : $line2) : $line3) : $line4) + 1;
            $this->rowCount = $num;
            $this->lineCount += $this->headLine;
        }

        return $this;
    }

    private function setTableData($data)
    {
        $objPHPExcel = $this->objPHPExcel;
        $this->data = $data;
        $line = $this->lineCount;
        $dataKV = $this->dataKV;
        foreach ($data as $val) {
            $row = ($this->hasIndex ? 1 : $this->dataHeadRow);//从第几列开始写数据
            if ($this->isDataKV) {
                for ($i = 0; $i < count($dataKV); $i++) {
                    if ($dataKV[$i] == '') {//空列 不赋值
                        $row++;
                        continue;
                    }
                    $objPHPExcel->setActiveSheetIndex()->setCellValue($this->getExcelIndex($row + 1) . $line, $val[$dataKV[$i]]);
                    $objPHPExcel->setActiveSheetIndex()->getstyle($this->getExcelIndex($row + 1) . $line)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $row++;
                }
            } else {
                foreach ($val as $k => $v) {
                    if ($v == '') {//空列 不赋值
                        $row++;
                        continue;
                    }
                    $objPHPExcel->setActiveSheetIndex()->setCellValue($this->getExcelIndex($row + 1) . $line, $v);
                    $objPHPExcel->setActiveSheetIndex()->getstyle($this->getExcelIndex($row + 1) . $line)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $row++;
                }
            }
            $line++;
        }
        $this->lineCount = $line;
        return $this;
    }


    /**
     * 第一列是否为序号列
     */
    private function hasIndex($index = true)
    {
        $objPHPExcel = $this->objPHPExcel;
        $index = $this->hasIndex;
        //$dataHead = $this->dataHead;
        $data = $this->data;
        $line = $this->lineCount;
        if ($index) {/*第一列为序号列*/
            if (empty($data)) {
                throw new Exception("数据不存在，请先执行setData(array \$data)");
            }
            $num = 0;
            foreach ($data as $val) {
                $num++;
                $objPHPExcel->setActiveSheetIndex()->setCellValue($this->getExcelIndex(1) . $line, $num);
                $objPHPExcel->setActiveSheetIndex()->getstyle($this->getExcelIndex(1) . $line)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $line++;
                continue;
            }
        }
        /* if (!empty($dataHead)) {//判断空 和 格式
             if (!is_array($dataHead)) {
                 throw new Exception("表体的头部格式不为array");
             }
             //调用表头方法，构建数据体的左边表头 //注意$line参数
             $line = $this->titleLine + $this->headLine;
             foreach ($data as $val) {
                 $this->setTableHead($dataHead, false, $line);
                 $line += count($val[0]);
             }
         }*/
        return $this;
    }

    /**
     * 执行导出
     * @param $isExcelObj false 执行导出;true 返回PHPExcel对象;PHPExcel对象 导出;default false
     * @return file or PHPExcel对象
     * @throws Exception
     */
    public function export($isExcelObj = false)
    {
        if ($isExcelObj === false) {
            if ($this->hasHead) {
                $this->setTableHead($this->head);
            }
            if ($this->hasTitle) {
                $this->title[0][2] = empty($this->title[0][2]) ? ($this->rowCount - 1) : $this->title[0][2];/*title跨列处理*/
                $this->setTableHead($this->title, false, 1);
            }
            if ($this->hasTotal && !$this->totalIsFoot) {//合计行
                $temp = $this->hasIndex;
                $isKV = $this->isDataKV;
                $data = $this->data;
                if ($temp) {
                    $this->hasIndex = false;
                }
                if ($isKV) {
                    $this->isDataKV = false;
                }
                $this->setTableData($this->dataTotal);
                $this->hasIndex = $temp;//恢复原参数，避免后面执行序号列错误
                $this->isDataKV = $isKV;//恢复原参数，避免后面执行表体数据错误
                $this->data = $data;
            }
            if ($this->hasIndex) {
                $this->hasIndex(true);
            } else {
                $this->hasIndex(false);
            }
            if ($this->hasData) {
                $this->setTableData($this->data);
            }
            if ($this->hasTotal && $this->totalIsFoot) {//合计行
                $temp = $this->hasIndex;
                $isKV = $this->isDataKV;
                $data = $this->data;
                if ($temp) {
                    $this->hasIndex = false;
                }
                if ($isKV) {
                    $this->isDataKV = false;
                }
                $this->setTableData($this->dataTotal);
                $this->hasIndex = $temp;//恢复原参数，避免后面执行序号列错误
                $this->isDataKV = $isKV;//恢复原参数，避免后面执行表体数据错误
                $this->data = $data;
            }
        } else if ($isExcelObj === true) {
            return $this->objPHPExcel;
        } else {
            $this->setExcelObj($isExcelObj);
            $this->export();
        }
        $this->setStyle();

        ob_end_clean(); //这一步非常关键，用来清除缓冲区防止导出的excel乱码
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . ($this->xlsName ? $this->xlsName : $this->fileName) . '.xls"');
        header("Content-Disposition:attachment;filename=" . $this->fileName . "." . ($this->excelType == "Excel2007" ? "xlsx" : "xls")); //"xls"参考下一条备注
        $objWriter = \PHPExcel_IOFactory::createWriter($this->objPHPExcel, $this->excelType); //"Excel2007"生成2007版本的xlsx，"Excel5"生成2003版本的xls
        $objWriter->save('php://output');
        exit;
    }

    /**
     * 读取Excel的内容
     * @param $filePath 文件完整路径
     * @param $exts 文件后缀
     * @param $sheet 读取的工作表 default=0（第一个）
     * @param $startCell 从哪列开始 default="A"(第一列)
     * @param $startRow 从哪行开始 default=1 （第一行）
     * @return $data Excel表格数据
     */
    public function readExcel($filePath, $exts = 'xls', $sheet = 0, $startCell = "A", $startRow = 1)
    {
        //如果excel文件后缀名为.xls，导入这个类
        if ($exts == 'xls') {
            Vendor('PHPExcel.PHPExcel.Reader.Excel5');
            $PHPReader = new \PHPExcel_Reader_Excel5();
        } else {
            Vendor('PHPExcel.PHPExcel.Reader.Excel2007');
            $PHPReader = new \PHPExcel_Reader_Excel2007();
        }

        //载入文件
        $PHPExcel = $PHPReader->load($filePath);
        //获取表中的工作表
        $currentSheet = $PHPExcel->getSheet($sheet);
        //获取总列数
        $allColumn = $currentSheet->getHighestColumn();
        //获取总行数
        $allRow = $currentSheet->getHighestRow();
        //交换数组中的键和值
        $header = array_flip($this->titleExcel());

        if (empty($header[$allColumn])) {
            throw new Exception("列" . $allColumn . "超出处理范围[A-JZ]");
        }
        //Excel数据
        $data = array();
        //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
        for ($currentRow = $startRow; $currentRow <= $allRow; $currentRow++) {
            //从哪列开始，A表示第一列
            for ($currentColumn = $header[$startCell]; $currentColumn <= $header[$allColumn]; $currentColumn++) {
                //数据坐标
                $Column = $this->getExcelIndex($currentColumn);
                $address = $Column . $currentRow;
                //读取到的数据，保存到数组$data中
                $cell = $currentSheet->getCell($address)->getValue();

                if ($cell instanceof \PHPExcel_RichText) {
                    $cell = $cell->__toString();
                }
                $data[$currentRow - $startRow][$Column] = $cell;
            }

        }
        return $data;
    }

    /**
     * 数据转xml
     */
    public function dataToXml($data, $root = true)
    {
        $str = "";
        if ($root) $str .= "<xml>";
        foreach ($data as $key => $val) {
            if (is_array($val)) {
                $child = $this->dataToXml($val, false);
                $str .= "<$key>$child</$key>";
            } else {
                $str .= "<$key><![CDATA[$val]]></$key>";
            }
        }
        if ($root) $str .= "</xml>";
        return $str;
    }

    /**
     * 数据转xml 使用DOMDocument
     */
    public function arrayToXml($arr, $dom = 0, $item = 0)
    {
        if (!$dom) {
            $dom = new \DOMDocument("1.0");
        }
        if (!$item) {
            $item = $dom->createElement("root");
            $dom->appendChild($item);
        }
        foreach ($arr as $key => $val) {
            $itemx = $dom->createElement(is_string($key) ? $key : "item");
            $item->appendChild($itemx);
            if (!is_array($val)) {
                $text = $dom->createTextNode($val);
                $itemx->appendChild($text);

            } else {
                $this->arrayToXml($val, $dom, $itemx);
            }
        }
        return $dom->saveXML();
    }

    function validxml()
    {
        libxml_use_internal_errors(true);
        $xmlFile = request()->file("xml");
//        if (empty($xmlFile)) {
//            $xmlstring = $_POST["xml"];//获取xml字符串
//        } else {
//            $xmlData = $this->readExcel($xmlFile);
//            $xmlstring = $this->dataToXml($xmlData);
//        }
        $xsdFile = request()->file("xsd");
        $xml = new \DOMDocument();
        $xml->loadXML($xmlFile); //读取xml
        $xsdname = '';
//        if ($_POST["xsd"] != "") {//传递xsd文件名
//            $xsdname = $_POST["xsd"];
//        } else {
//            $xsdstring = $_POST["xsdString"];//否则读取xsd字符串
//            $myfile = fopen("valid.xsd", "w") or die("Unable to open file!");//打开xsd文件
//            fwrite($myfile, $xsdstring);//写入字符串
//            fclose($myfile);//关闭
//            $xsdname = './valid.xsd';//设置文件名
//        }
        if (!empty($xsdFile)) {
            $xsdname = $xsdFile;
        }
        if (!$xml->schemaValidate($xsdname->getRealPath())) {//验证
            self::libxml_display_errors();
        } else {
            return "验证通过且格式良好";
        }
    }

    private function libxml_display_errors()
    {
        $errors = libxml_get_errors();
        $return = "";
        foreach ($errors as $error) {
            $return .= "<br/>";
            switch ($error->level) {
                case LIBXML_ERR_WARNING:
                    $return .= "<b>Warning $error->code</b>: ";
                    break;
                case LIBXML_ERR_ERROR:
                    $return .= "<b>Error $error->code</b>: ";
                    break;
                case LIBXML_ERR_FATAL:
                    $return .= "<b>Fatal Error $error->code</b>: ";
                    break;
            }
            $return .= trim($error->message); //获取错误
            if ($error->file) {
                $return .= " in <b>xml file</b>";
            }
            $return .= " on line <b>$error->line</b>";  //获取错误位置
        }
        libxml_clear_errors();
        return $return;//返回值
    }


    /**
     * 设置样式
     * @param array $style 样式数组
     * @param null $col 行
     * @param null $row 列
     * @return $this
     */
    private function setStyle(array $style = null, $col = null, $row = null)
    {
        $objPHPExcel = $this->objPHPExcel;
        if (empty($style)) {
            $style = array(
                'borders' => array(
                    'allborders' => array(
                        //'style' => \PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
                        'style' => \PHPExcel_Style_Border::BORDER_THIN, //细边框
                        'color' => array('argb' => '00000000'),
                    ),
                ),
            );
        }
        //表头行（A1）字体加粗
        //$objPHPExcel->getActiveSheet()->getStyle('A1:' . $this->getExcelIndex($this->rowCount - 1) . "1")->getFont()->setSize(16)->setBold(true);
        //边框
        $objPHPExcel->getActiveSheet()->getStyle('A1:' . $this->getExcelIndex($this->rowCount - 1) . ($this->lineCount - 1))->applyFromArray($style);
        $objPHPExcel->getActiveSheet()->getStyle('A1:' . $this->getExcelIndex($this->rowCount - 1) . ($this->lineCount - 1))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A1:' . $this->getExcelIndex($this->rowCount - 1) . ($this->lineCount - 1))->getAlignment()->setWrapText(true); //自动换行

        //$objPHPExcel->getActiveSheet()->getColumnDimension()->setAutoSize(true);

        return $this;
    }


    /**
     * 设置Excel的大标题
     * @param array|string $title
     */
    public function setTitle($title = "")
    {   //格式处理
        if (is_string($title)) {
            $title = [[$title, 1]];
        }
        if (!is_array($title[0])) {/*格式问题 [[]]*/
            $title[0] = $title;
            unset($title[1]);
            unset($title[2]);
        }
        $this->title = $title;
        $this->titleLine = count($title);
        $this->hasTitle = true;
        return $this;
    }

    /**
     * 第一列是否为序号列
     * @param bool $hasIndex
     */
    public function setHasIndex($hasIndex = true)
    {
        $this->hasIndex = $hasIndex;
        return $this;
    }

    /**
     * 设置导出的数据
     * @param array $data
     * @param bool $hasIndex 第一列是否为序号
     */
    public function setData(array $data, $hasIndex = true)
    {
        $this->data = $data;
        $this->hasData = true;
        $this->hasIndex = $hasIndex;
        $this->isDataHead = false;
        return $this;
    }

    /**
     * 设置导出的表头
     * @param array|string $head
     */
    public function setHead(array $head)
    {
        $this->head = $head;
        $this->hasHead = true;
        return $this;
    }

    /**
     * 设置数据部分的数据头
     * @param mixed $dataHead
     */
    public function setDataHead(array $dataHead)
    {
        $this->dataHead = $dataHead;
        $this->isDataHead = true;
        $this->hasIndex = false;
        return $this;
    }

    /**
     * 设置文件名
     * @param string $fileName
     */
    public function setFileName($fileName)
    {
        $this->fileName = $fileName;
        return $this;
    }

    /**
     * 设置Excel显示名
     * @param string $xlsName
     */
    public function setXlsName($xlsName)
    {
        $this->xlsName = $xlsName;
        /*if (is_string($xlsName)) {
            $this->titleLine += 1;
        } else {
            $this->titleLine += count($xlsName);
            $this->setTableHead($xlsName, false);
        }*/
        return $this;
    }

    /**
     * 设置导出的Excel的版本 xls | xlsx
     * @param string $excelType
     */
    public function setExcelType($excelType)
    {
        $this->excelType = $excelType;
        return $this;
    }

    /**
     * 设置数据与表头的字段对应
     * @param bool $isDataKV
     */
    public function setDataKV(array $dataKV)
    {
        $this->dataKV = $dataKV;
        $this->isDataKV = true;
        return $this;
    }

    /**
     * 返回PHPExcel实例用于外部操作
     * 主要用来修改样式
     * @return \PHPExcel
     */
    public function getExcelObj()
    {
        return $this->objPHPExcel;
    }

    /**
     * 设置PHPExcel对象
     * @param $PHPExcel
     */
    public function setExcelObj($PHPExcel)
    {
        $this->objPHPExcel = $PHPExcel;
        return $this;
    }

    public function setDataHeadRow($row = 1)
    {
        $this->dataHeadRow = $row;
        return $this;
    }

    private function titleExcel()
    {
        /**  excel列名通用 */
        $excel_list = [1 => 'A', 2 => 'B', 3 => 'C', 4 => 'D', 5 => 'E', 6 => 'F', 7 => 'G', 8 => 'H', 9 => 'I', 10 => 'J', 11 => 'K', 12 => 'L', 13 => 'M', 14 => 'N', 15 => 'O', 16 => 'P', 17 => 'Q', 18 => 'R', 19 => 'S', 20 => 'T', 21 => 'U', 22 => 'V', 23 => 'W', 24 => 'X', 25 => 'Y', 26 => 'Z',
            27 => 'AA', 28 => 'AB', 29 => 'AC', 30 => 'AD', 31 => 'AE', 32 => 'AF', 33 => 'AG', 34 => 'AH', 35 => 'AI', 36 => 'AJ', 37 => 'AK', 38 => 'AL', 39 => 'AM', 40 => 'AN', 41 => 'AO', 42 => 'AP', 43 => 'AQ', 44 => 'AR', 45 => 'AS', 46 => 'AT', 47 => 'AU', 48 => 'AV', 49 => 'AW', 50 => 'AX', 51 => 'AY', 52 => 'AZ',
            53 => 'BA', 54 => 'BB', 55 => 'BC', 56 => 'BD', 57 => 'BE', 58 => 'BF', 59 => 'BG', 60 => 'BH', 61 => 'BI', 62 => 'BJ', 63 => 'BK', 64 => 'BL', 65 => 'BM', 66 => 'BN', 67 => 'BO', 68 => 'BP', 69 => 'BQ', 70 => 'BR', 71 => 'BS', 72 => 'BT', 73 => 'BU', 74 => 'BV', 75 => 'BW', 76 => 'BX', 77 => 'BY', 78 => 'BZ',
            79 => 'CA', 80 => 'CB', 81 => 'CC', 82 => 'CD', 83 => 'CE', 84 => 'CF', 85 => 'CG', 86 => 'CH', 87 => 'CI', 88 => 'CJ', 89 => 'CK', 90 => 'CL', 91 => 'CM', 92 => 'CN', 93 => 'CO', 94 => 'CP', 95 => 'CQ', 96 => 'CR', 97 => 'CS', 98 => 'CT', 99 => 'CU', 100 => 'CV', 101 => 'CW', 102 => 'CX', 103 => 'CY', 104 => 'CZ',
            105 => 'DA', 106 => 'DB', 107 => 'DC', 108 => 'DD', 109 => 'DE', 110 => 'DF', 111 => 'DG', 112 => 'DH', 113 => 'DI', 114 => 'DJ', 115 => 'DK', 116 => 'DL', 117 => 'DM', 118 => 'DN', 119 => 'DO', 120 => 'DP', 121 => 'DQ', 122 => 'DR', 123 => 'DS', 124 => 'DT', 125 => 'DU', 126 => 'DV', 127 => 'DW', 128 => 'DX', 129 => 'DY', 130 => 'DZ',
            131 => 'EA', 132 => 'EB', 133 => 'EC', 134 => 'ED', 135 => 'EE', 136 => 'EF', 137 => 'EG', 138 => 'EH', 139 => 'EI', 140 => 'EJ', 141 => 'EK', 142 => 'EL', 143 => 'EM', 144 => 'EN', 145 => 'EO', 146 => 'EP', 147 => 'EQ', 148 => 'ER', 149 => 'ES', 150 => 'ET', 151 => 'EU', 152 => 'EV', 153 => 'EW', 154 => 'EX', 155 => 'EY', 156 => 'EZ',
            157 => 'FA', 158 => 'FB', 159 => 'FC', 160 => 'FD', 161 => 'FE', 162 => 'FF', 163 => 'FG', 164 => 'FH', 165 => 'FI', 166 => 'FJ', 167 => 'FK', 168 => 'FL', 169 => 'FM', 170 => 'FN', 171 => 'FO', 172 => 'FP', 173 => 'FQ', 174 => 'FR', 175 => 'FS', 176 => 'FT', 177 => 'FU', 178 => 'FV', 179 => 'FW', 180 => 'FX', 181 => 'FY', 182 => 'FZ',
            183 => 'GA', 184 => 'GB', 185 => 'GC', 186 => 'GD', 187 => 'GE', 188 => 'GF', 189 => 'GG', 190 => 'GH', 191 => 'GI', 192 => 'GJ', 193 => 'GK', 194 => 'GL', 195 => 'GM', 196 => 'GN', 197 => 'GO', 198 => 'GP', 199 => 'GQ', 200 => 'GR', 201 => 'GS', 202 => 'GT', 203 => 'GU', 204 => 'GV', 205 => 'GW', 206 => 'GX', 207 => 'GY', 208 => 'GZ',
            209 => 'HA', 210 => 'HB', 211 => 'HC', 212 => 'HD', 213 => 'HE', 214 => 'HF', 215 => 'HG', 216 => 'HH', 217 => 'HI', 218 => 'HJ', 219 => 'HK', 220 => 'HL', 221 => 'HM', 222 => 'HN', 223 => 'HO', 224 => 'HP', 225 => 'HQ', 226 => 'HR', 227 => 'HS', 228 => 'HT', 229 => 'HU', 230 => 'HV', 231 => 'HW', 232 => 'HX', 233 => 'HY', 234 => 'HZ',
            235 => 'IA', 236 => 'IB', 237 => 'IC', 238 => 'ID', 239 => 'IE', 240 => 'IF', 241 => 'IG', 242 => 'IH', 243 => 'II', 244 => 'IJ', 245 => 'IK', 246 => 'IL', 247 => 'IM', 248 => 'IN', 249 => 'IO', 250 => 'IP', 251 => 'IQ', 252 => 'IR', 253 => 'IS', 254 => 'IT', 255 => 'IU', 256 => 'IV', 257 => 'IW', 258 => 'IX', 259 => 'IY', 260 => 'IZ',
            261 => 'JA', 262 => 'JB', 263 => 'JC', 264 => 'JD', 265 => 'JE', 266 => 'JF', 267 => 'JG', 268 => 'JH', 269 => 'JI', 270 => 'JJ', 271 => 'JK', 272 => 'JL', 273 => 'JM', 274 => 'JN', 275 => 'JO', 276 => 'JP', 277 => 'JQ', 278 => 'JR', 279 => 'JS', 280 => 'JT', 281 => 'JU', 282 => 'JV', 283 => 'JW', 284 => 'JX', 285 => 'JY', 286 => 'JZ'
        ];
        return $excel_list;
    }

    /**
     * @param int $i 1-286
     * @return 对应的Excel表的列索引值
     * @throws Exception 超出索引
     */
    public function getExcelIndex($i = 1)
    {
        if ($i <= 0 || $i > 286) {
            throw new Exception("输入的索引[" . $i . "]超出所定义的Excel列范围[1-286]");
        }
        return $this->titleExcel()[$i];
    }

    /**
     * @param array $dataTotal
     */
    public function setDataTotal(array $dataTotal, $totalIsFoot = false)
    {
        $this->totalIsFoot = $totalIsFoot;
        $this->hasTotal = true;
        $this->dataTotal = $dataTotal;
        return $this;
    }
}